<?php

/**
 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $
 * @package		Joomla16.Tutorials
 * @subpackage	Components
 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.
 * @author		Christophe Demko
 * @link		http://joomlacode.org/gf/project/helloworld_1_6/
 * @license		License GNU General Public License version 2 or later
 */

// No direct access to this file
defined('_JEXEC') or die('Restricted access');

// import Joomla modelitem library
jimport('joomla.application.component.modelitem');

/**
 * HelloWorld Model
 */
class bgModelbg extends JModel
{   
    /**
     * Get all area
     */
    function getArea(){
        $this->_db->setQuery("SELECT * FROM area ORDER BY AREA_CODE ASC");
        return $this->_db->loadObjectList();
    }
    
    /**
     * Get all Pref by Area
     */
    function getPrefsByArea($area_code){
        $this->_db->setQuery("SELECT * FROM pref WHERE PREF_AREA_CODE = $area_code");             
        return $this->_db->loadObjectList();  
    }
    
    /**
     * Get all pref
     */
    function getPrefs(){
        $this->_db->setQuery("SELECT * FROM pref");             
        return $this->_db->loadObjectList();  
    }
    
    /**
     * Get all USCL (Center)
     */
    public function getAllUscm(){
        $this->_db->setQuery("SELECT * FROM uscm WHERE USCM_FLAG = 1");
        return $this->_db->loadObjectList();
    }
    
    /**
     * Get all USFM (Center)
     */
    public function getAllUsfm(){
        $this->_db->setQuery("SELECT * FROM usfm ORDER BY USFM_CODE");
        $result = $this->_db->loadObjectList();
        return $result;
    }
    
    /**
     * Get all PACM (Club)
     */
    public function getAllPacm(){
        $this->_db->setQuery("SELECT * FROM pacm ");
        return $this->_db->loadObjectList();
    }
    
    /**
     * Get all PACM (Club)
     */
    public function getAllActm(){
        $this->_db->setQuery("SELECT * FROM actm");
        $result = $this->_db->loadObjectList();
        return $result;
    }
    
    /**
     * Get USCL value for display and update
     */
    public function getUscl($month, $year, $ocba_code){
        $data = array(
            'month' => $month,
            'year'  => $year,
            'ocba_code' => $ocba_code,
        );
        $date = $data['year']."/".$data['month'];
        
        $sql = "SELECT * FROM uscl
                WHERE 
                    USCL_OCBA_CODE = '$data[ocba_code]' 
                    AND USCL_DATE = '$date'
                ";
        
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        foreach ($result as $row){
            $data['uscl_data'][$row->USCL_PACM_CODE][$row->USCL_ACTM_CODE] = $row->USCL_VAL;
        }
        
        $this->_db->setQuery("SELECT OCBA_NAME FROM ocba WHERE OCBA_CODE = '$data[ocba_code]'");
        $ocba = $this->_db->loadObject();
        if(!empty($ocba)){
            $data['ocba_name'] = $ocba->OCBA_NAME;
        }
        
        return $data;
        
    }
    
    /**
     * Save USCL. insert for new, fallback into update on exits
     */
    public function saveUscl($data){
        //save (insert) data, fallback to update if index exits
        foreach($data['uscl_data'] as $pa_key => $pa){
            foreach($pa as $ac_key => $ac){
                $uscl_code = HelperBG::getIDAuto('uscl','USCL_CODE');
                $sql = "INSERT INTO uscl (USCL_CODE, USCL_OCBA_CODE, USCL_DATE, USCL_PACM_CODE, USCL_ACTM_CODE, USCL_VAL)
                VALUE ($uscl_code, '$data[ocba_code]', '$data[date]', $pa_key, $ac_key, $ac )
                ON DUPLICATE key UPDATE USCL_VAL = $ac, USCL_UPDATE = NOW()";
                //echo $sql."<br>";
                $this->_db->setQuery($sql);
                $this->_db->query();
            }
        }
       // die;
    }
    
    
    /**
     * Get all USFM belong to OCBA
     */
    public function getUsfmByOcba($ocba_code){
        $this->_db->setQuery("SELECT * FROM usfm ORDER BY USFM_CODE");
        $result = $this->_db->loadObjectList();
        foreach($result as $key => $row){
            if($row->USFM_NAME == '第一体育館' && $this->have_gym1($ocba_code))
                unset($result[$key]);
            if($row->USFM_NAME == '第二体育館' && $this->have_gym2($ocba_code))
                unset($result[$key]);
            if($row->USFM_NAME == 'プール' && $this->have_pool($ocba_code))
                unset($result[$key]);
            if($row->USFM_NAME == '艇庫' && $this->have_dock($ocba_code))
                unset($result[$key]);
        }
        return $result;
    }
    
    /**
     * Check if OCBA have Gym1
     */
    public function have_gym1($ocba_code){
        $this->_db->setQuery("SELECT GYMT_CODE FROM gymt WHERE GYMT_OCBA_CODE = $ocba_code AND GYMT_2NDK = 1");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Gym2
     */
    public function have_gym2($ocba_code){
        $this->_db->setQuery("SELECT GYMT_CODE FROM gymt WHERE GYMT_OCBA_CODE = $ocba_code AND GYMT_2NDK = 2");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Pool
     */
    public function have_pool($ocba_code){
        $this->_db->setQuery("SELECT POOL_CODE FROM pool WHERE POOL_OCBA_CODE = $ocba_code");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Check if OCBA have Dock
     */
    public function have_dock($ocba_code){
        $this->_db->setQuery("SELECT DOCK_CODE FROM dock WHERE DOCK_OCBA_CODE = $ocba_code");
        $result = $this->_db->loadObjectList();
        return !empty($result);
    }
    
    /**
     * Save USCE, insert on new, fallback into update on exits
     */
    public function saveUsce($data){
        //save (insert) data, fallback to update if index exits
        foreach($data['usce_data'] as $cm_key => $cm){
            foreach($cm as $cf_key => $cf){
                $usce_code = HelperBG::getIDAuto('usce','USCE_CODE');
                $sql = "INSERT INTO usce (USCE_CODE, USCE_OCBA_CODE, USCE_DATE, USCE_USCM_CODE, USCE_USFM_CODE, USCE_VAL)
                VALUE ($usce_code, '$data[ocba_code]', '$data[date]', $cm_key, $cf_key, $cf )
                ON DUPLICATE key UPDATE USCE_VAL = $cf, USCE_UPDATE = NOW()";
                $this->_db->setQuery($sql);
                $this->_db->query();
            }
        }
    }
    
    /**
     * Get USCE for view and edit
     */
    public function getUsce($month, $year, $ocba_code){
        $data = array(
            'month' => $month,
            'year'  => $year,
            'ocba_code' => $ocba_code,
        );
        $d = $data['year']."/".$data['month'];
        
        $sql = "SELECT * FROM usce 
            
        WHERE 
            USCE_OCBA_CODE = '$data[ocba_code]' 
            AND USCE_DATE = '$d'
        ";
        
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        foreach ($result as $row){
            $data['usce_data'][$row->USCE_USCM_CODE][$row->USCE_USFM_CODE] = $row->USCE_VAL;
        }
        
        $this->_db->setQuery("SELECT OCBA_NAME FROM ocba WHERE OCBA_CODE = '$data[ocba_code]'");
        $ocba = $this->_db->loadObject();
        if(!empty($ocba)){
            $data['ocba_name'] = $ocba->OCBA_NAME;
        }
        
        return $data;
        
    }
    
    /**
     * Get ocba list as code list with pref_code = $pref
     */
    public function getOcbaList($pref ){
        $where_pref = $pref == 'all' ? "" : "WHERE TOWN_PREF_CODE = $pref";
        $sql =  "SELECT DISTINCT OCBA_CODE FROM ocba LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE $where_pref AND LEFT(OCBA_CODE,1) = '0' ";
        $this->_db->setQuery($sql);
        $result = $this->_db->loadObjectList();
        if(!empty($result)){
            foreach($result as $k => $v){
                $tmp[$k] = "'".$v->OCBA_CODE."'";
            }
        } 
        return @implode(", ",$tmp);       
    }
    
    /**
     * Load USCE for statistic
     */
    public function loadUsce($filter){
        
        //get the DB object
        $db = $this->_db;
        $data = array();
        $data['chart_data'][0] = array();
        $last_update = 0;

        //get list of OCBA 
        $where_ocba = "";
        $where_ocba .= $filter['area'] == 'all' ? "" : "AND PREF_AREA_CODE = $filter[area] ";
        $where_ocba .= $filter['pref'] == 'all' ? "" : "AND PREF_CODE = $filter[pref] ";
        $where_ocba .= $filter['ocba_name'] == '' ? "" : "AND (OCBA_NAME LIKE '%$filter[ocba_name]%' OR OCBA_KANA LIKE '%$filter[ocba_name]%') ";
        $where_ocba .= $filter['ocba_code'] == '' ? "" : "AND OCBA_CODE = '$filter[ocba_code]' ";
        
        $ocba = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        LEFT JOIN pref  ON TOWN_PREF_CODE = PREF_CODE 
                    WHERE OCBA_FLAG = 1 $where_ocba AND LEFT(OCBA_CODE,1) = '0') ";
        $db->setQuery($ocba);
        $ocbas = $db->loadObjectList();
        
        if(count($ocbas) == 1){
            $data['ocba_name'] = $ocbas[0]->OCBA_NAME;
        }

        foreach($ocbas as $key => $ocba){
            $tmp[$key] = "'".$ocba->OCBA_CODE."'";            
        }        
        $ocba_code_list = @implode(", ",$tmp);        
        
        //get facility
        $where_facility = "";
        switch($filter['facility']){               
            case 'all_gym':
                $where_facility .= " AND USFM_KNAME = '体育館'";
                break;
                
            case 'gym1':
                $where_facility .= " AND USFM_NAME = '第一体育館'";
                break;
                
            case 'gym2':
                $where_facility .= " AND USFM_NAME = '第二体育館'";
                break;
                
            case 'pool':
                $where_facility .= " AND USFM_KNAME = 'プール'";
                break;
                
            case 'dock':
                $where_facility .= " AND USFM_KNAME = '艇庫'";
                break;
            case 'all':
            default:
                break;
        }
        $facility = "(SELECT * FROM usfm WHERE 1=1 $where_facility)";
        $db->setQuery($facility);
        $facility_list = $db->loadObjectList();
        
        //get category
        $category = "(SELECT * FROM uscm WHERE USCM_FLAG = 1)";
        $db->setQuery($category);
        $category_list = $db->loadObjectList();
        
        $start_month = $filter['period_f'] == 1 ? (int)$filter['month'] : 4;
        $start_year = $filter['year'];
        $end_month = "";
        $end_year = "";
        $end = "";

        
        //foreach period, calculate it's value
        for ($period = 1; $period <= $filter['period']; $period++){   
            $end_year = $start_year;
            $end_month = $start_month + $filter['period_f'] - 1;
            if($end_month > 12){
                $end_month -= 12;
                $end_year++;
            }
                        
            $start = $start_year."/".str_pad($start_month,2,'0',STR_PAD_LEFT);
            $end = $end_year."/".str_pad($end_month,2,'0',STR_PAD_LEFT);
            
            //put the head of row
            switch($filter['period_f']){
                case 1:
                    $data['head'][$period] = $start_month."月";
                break;
                
                case 3:
                    switch($start_month){
                        case 4:
                            $data['head'][$period] = $start_year."年第一四半期";
                        break;
                        
                        case 7:
                            $data['head'][$period] = $start_year."年第二四半期";
                        break;
                        
                        case 10:
                            $data['head'][$period] = $start_year."年第三四半期";
                        break;
                        
                        case 1:
                            $data['head'][$period] = ($start_year - 1)."年第四四半期";
                        break;
                    }
                break;
                
                case 6:
                    if($start_month == 4){
                        $data['head'][$period] = $start_year."年上期";
                    } else if($start_month == 10){
                        $data['head'][$period] = $start_year."年下期";
                    }
                break;
                
                case 12:
                    $data['head'][$period] = $start_year."年";
                break;
            }
            //$data['head'][$period] = $filter['period_f'] == 1 ? $start_month."月" : $start_month." ~ ".$end_month."月";            
            $tmp_chart_data = array();            
            array_push($tmp_chart_data,$data['head'][$period]);
            
            //set time (for query)
            $time = $start == $end ? " AND USCE_DATE = '$start' " : " AND USCE_DATE >= '$start' AND USCE_DATE <= '$end' ";
            
            //foreach uscm avaiable
            foreach($category_list as $category_key => $category_row){   
                $sql = 
                    "SELECT SUM(USCE_VAL) as val, MAX(USCE_UPDATE)as last_update FROM usce 
                        JOIN $facility as facility ON USCE_USFM_CODE = USFM_CODE
                        JOIN $category as category ON USCE_USCM_CODE = USCM_CODE
                        WHERE 
                            USCE_OCBA_CODE IN ($ocba_code_list) 
                            $time
                            AND USCM_CODE = $category_row->USCM_CODE
                        ";
                
                $db->setQuery($sql);

                $result = $db->loadObject();
                $val = empty($result->val) ? '-' : $result->val;
                if(!empty($result->last_update) && (strtotime($result->last_update) > $last_update)){
                    $last_update = strtotime($result->last_update);
                }
                
                $data['usce_data'][$category_row->USCM_CODE][$period] = $val;
                
                //if numf == 1 then add the value to sum_col then push the value to tmp_chart_data
                if($category_row->USCM_NUMF == 1){
                    $data['sum_col'][$period] += $val;
                    array_push($tmp_chart_data,(int)$val);
                }
                //add the value to sum_row (without check numf)
                $data['sum_row'][$category_row->USCM_CODE] += $val; 
            }
            array_push($tmp_chart_data,$data['sum_col'][$period]);
            
            
            //calculate national average
            $ocba_nation_list = $this->getOcbaList('all');                     
            $avg_nation_sql = 
                    "SELECT SUM(USCE_VAL) as val FROM usce 
                        JOIN $facility as facility ON USCE_USFM_CODE = USFM_CODE
                        JOIN $category as category ON USCE_USCM_CODE = USCM_CODE AND USCM_NUMF = 1
                        WHERE 
                            USCE_OCBA_CODE IN (".$ocba_nation_list.") 
                            $time
                        ";
            $db->setQuery($avg_nation_sql);
            $avg_nation = $db->loadObject();
            
            array_push($tmp_chart_data,empty($avg_nation->val) ? 0 : (int)($avg_nation->val/count(explode(', ',$ocba_nation_list))));
            
            
            //calculate pref average
            if($filter['pref'] != 'all'){
                $ocba_pref_list = $this->getOcbaList($filter['pref']);
                //echo $ocba_pref_list;
                $avg_pref_sql = 
                    "SELECT SUM(USCE_VAL) as val FROM usce 
                        JOIN $facility as facility ON USCE_USFM_CODE = USFM_CODE
                        JOIN $category as category ON USCE_USCM_CODE = USCM_CODE AND USCM_NUMF = 1
                        WHERE 
                            USCE_OCBA_CODE IN (".$ocba_pref_list.") 
                            $time
                        ";
                $db->setQuery($avg_pref_sql);
                $avg_pref = $db->loadObject();
                
                array_push($tmp_chart_data,empty($avg_pref->val) ? 0 : (int)($avg_pref->val/count(explode(', ',$ocba_pref_list))));
            }
            
            array_push($data['chart_data'],$tmp_chart_data);
            
            //start date = end date + 1
            $start_year = $end_year;
            $start_month = $end_month + 1;
                if($start_month > 12){
                    $start_month -= 12;
                    $start_year++;
                }
            
        }   
        $data['range'] = "$filter[year]年$filter[month]月 ~ $end_year"."年".str_pad($end_month,2,'0',STR_PAD_LEFT)."月";
        $data['last_update'] = $last_update == 0 ? "----年--月--日" : date('Y年m月d日',$last_update);   
        
        //replace '0' -> '-' for sum (row and column)
        foreach($data['sum_row'] as $k => $row){
            if($row == 0){
                $data['sum_row'][$k] = '-';
            }
        }        
        foreach($data['sum_col'] as $k => $row){
            if($row == 0){
                $data['sum_col'][$k] = '-';
            }
        }
        
         //push header for chart 
        array_push($data['chart_data'][0],"ジャンル");
        $data['chart_num_col'] = 0;
        foreach($category_list as $category_key => $category_row){
            if($category_row->USCM_NUMF == 1){
               array_push($data['chart_data'][0],$category_row -> USCM_NAME); 
               $data['chart_num_col']++;
            }
        }
        array_push($data['chart_data'][0],'自センター累計');
        $data['chart_num_col']++;
        array_push($data['chart_data'][0],'全国平均');
        $data['chart_disp'] = 1;
        $data['chart_num_col']++;
        if($filter['pref'] != 'all'){            
            $data['chart_disp'] = 2;
            $data['chart_num_col']++;
            array_push($data['chart_data'][0],'県平均');
        }
        
        $data['period'] = $filter['period'];
        return $data;
        
    }
    
    /**
     * Load USCL for statistic
     */
    public function loadUscl($filter){
        //get the DB object
        $db = $this->_db;
        $data = array();
        $last_update = 0; 
        //get list of OCBA 
        $where_ocba = "";
        $where_ocba .= $filter['area'] == 'all' ? "" : "AND PREF_AREA_CODE = $filter[area] ";
        $where_ocba .= $filter['pref'] == 'all' ? "" : "AND PREF_CODE = $filter[pref] ";
        $where_ocba .= $filter['ocba_name'] == '' ? "" : "AND (OCBA_NAME LIKE '%$filter[ocba_name]%' OR OCBA_KANA LIKE '%$filter[ocba_name]%') ";
        $where_ocba .= $filter['ocba_code'] == '' ? "" : "AND OCBA_CODE = '$filter[ocba_code]' ";
        
        $ocba = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        LEFT JOIN pref  ON TOWN_PREF_CODE = PREF_CODE 
                    WHERE OCBA_FLAG = 1 $where_ocba AND LEFT(OCBA_CODE,1) = '1') ";
        $db->setQuery($ocba);
        //echo $ocba;
        $ocbas = $db->loadObjectList();
        if(count($ocbas) == 1){
            $data['ocba_name'] = $ocbas[0]->OCBA_NAME;
        }
        foreach($ocbas as $key => $ocba){
            $tmp[$key] = "'".$ocba->OCBA_CODE."'";            
        }
        $ocba_code_list = @implode(", ",$tmp);        
        
        //get activity

        $activity = "(SELECT * FROM actm)";
        $db->setQuery($activity);
        $activity_list = $db->loadObjectList();
        
        //get category
        $category = "(SELECT * FROM pacm )";
        $db->setQuery($category);
        $category_list = $db->loadObjectList();
        
        //caculate start and end time
        $start_month = (int)$filter['month'];
        $start_year = $filter['year'];
        $end_month = "";
        $end_year = "";
        $end = "";
        
        //$data 
               

        for ($period = 1; $period <= $filter['period']; $period++){   
            $end_year = $start_year;
            $end_month = $start_month + $filter['period_f'] - 1;
            if($end_month > 12){
                $end_month -=12;
                $end_year++;
            }
                        
            $start = $start_year."/".str_pad($start_month,2,'0',STR_PAD_LEFT);
            $end = $end_year."/".str_pad($end_month,2,'0',STR_PAD_LEFT);

            //put the head of row
            switch($filter['period_f']){
                case 1:
                    $data['head'][$period] = $start_month."月";
                break;
                
                case 3:
                    switch($start_month){
                        case 4:
                            $data['head'][$period] = $start_year."年第一四半期";
                        break;
                        
                        case 7:
                            $data['head'][$period] = $start_year."年第二四半期";
                        break;
                        
                        case 10:
                            $data['head'][$period] = $start_year."年第三四半期";
                        break;
                        
                        case 1:
                            $data['head'][$period] = ($start_year - 1)."年第四四半期";
                        break;
                    }
                break;
                
                case 6:
                    if($start_month == 4){
                        $data['head'][$period] = $start_year."年上期";
                    } else if($start_month == 10){
                        $data['head'][$period] = $start_year."年下期";
                    }
                break;
                
                case 12:
                    $data['head'][$period] = $start_year."年";
                break;
            }          
            
            $time = $start == $end ? " USCL_DATE = '$start' " : " USCL_DATE >= '$start' AND USCL_DATE <= '$end' ";
            foreach ($activity_list as $activity_key => $activity_row){
                //foreach PACM avaiable, get value
                foreach($category_list as $category_key => $category_row){   
                    $sql = 
                        "SELECT SUM(USCL_VAL) as val, MAX(USCL_UPDATE)as last_update FROM uscl 
                            JOIN $activity as activity ON USCL_ACTM_CODE = ACTM_CODE
                            JOIN $category as category ON USCL_PACM_CODE = PACM_CODE
                            WHERE 
                                USCL_OCBA_CODE IN ($ocba_code_list) 
                                AND $time
                                AND PACM_CODE = $category_row->PACM_CODE
                                AND ACTM_CODE = $activity_row->ACTM_CODE
                            ";      
                    $db->setQuery($sql);
                    $result = $db->loadObject();
                    $val = empty($result->val) ? '-' : $result->val;
                    if(!empty($result->last_update) && (strtotime($result->last_update) > $last_update)){
                        $last_update = strtotime($result->last_update);
                    }
                    
                    //assign data value
                    $data['uscl_data'][$activity_row->ACTM_CODE][$category_row->PACM_CODE][$period] = $val;
                    if($category_row->PACM_NUMF == 1){
                        $data['sum_col'][$period] += $val;
                    } else {
                        $data['sum_col2'][$period] += $val;
                    }
                    $data['sum_row'][$activity_row->ACTM_CODE][$category_row->PACM_CODE] += $val; 
                }
            }
            
            $start_year = $end_year;
            $start_month = $end_month + 1;
                if($start_month > 12){
                    $start_month -= 12;
                    $start_year++;
                }
            
        }   
        $data['range'] = "$filter[year]年$filter[month]月 ~ $end_year"."年".str_pad($end_month,2,'0',STR_PAD_LEFT)."月";
        $data['last_update'] = $last_update == 0 ? "----年--月--日" : date('Y年m月d日',$last_update);   
        
        //replace '0' => '-' for sum (row and col)
        foreach($data['sum_row'] as $k => $row){
            if($row == 0){
                $data['sum_row'][$k] = '-';
            }
        }      
        foreach($data['sum_col'] as $k => $row){
            if($row == 0){
                $data['sum_col'][$k] = '-';
            }
        }
        foreach($data['sum_col2'] as $k => $row){
            if($row == 0){
                $data['sum_col2'][$k] = '-';
            }
        }
          
        
        $data['period'] = $filter['period'];
        return $data;
    }
    
    /**
     * Rank center
     */
    public function rank_center($filter) {
        $data = array();
        $last_update = 0;
        
        //get the DB object
        $db = $this->_db;
        
        //get list of OCBA 
        $where_ocba = "";
        $where_ocba .= $filter['area'] == 'all' ? "" : "AND PREF_AREA_CODE = $filter[area] ";
        $where_ocba .= $filter['pref'] == 'all' ? "" : "AND PREF_CODE = $filter[pref] ";
        
        $ocba_sql = "SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        LEFT JOIN pref  ON TOWN_PREF_CODE = PREF_CODE 
                    WHERE OCBA_FLAG = 1 $where_ocba AND LEFT(OCBA_CODE,1) = '0'";
        $db->setQuery($ocba_sql);
        $ocbas = $db->loadObjectList();

        $where_facility = "";
        switch($filter['facility']){               
            case 'all_gym':
                $where_facility .= " AND USFM_KNAME = '体育館'";
                break;
                
            case 'gym1':
                $where_facility .= " AND USFM_NAME = '第一体育館'";
                break;
                
            case 'gym2':
                $where_facility .= " AND USFM_NAME = '第二体育館'";
                break;
                
            case 'pool':
                $where_facility .= " AND USFM_KNAME = 'プール'";
                break;
                
            case 'dock':
                $where_facility .= " AND USFM_KNAME = '艇庫'";
                break;
            case 'all':
            default:
                break;
        }
        //$facility_sql = "(SELECT * FROM usfm WHERE 1=1 $where_facility)";
        $ranking = array();
        foreach ($ocbas as $ocba) {
            $sql = "SELECT SUM(USCE_VAL) AS val ".
                "FROM usce ".
                "JOIN uscm ON USCE_USCM_CODE = USCM_CODE AND USCM_NUMF = 1 ".
                "JOIN usfm ON USCE_USFM_CODE = USFM_CODE $where_facility ".
                "WHERE USCE_OCBA_CODE = '$ocba->OCBA_CODE' AND USCE_DATE >= '$filter[from]' AND USCE_DATE <= '$filter[to]' ".
                "GROUP BY USCE_OCBA_CODE ORDER BY val DESC " ;
            $db->setQuery($sql);
            $count = $db->loadObject();   
            array_push($ranking, (object)array(
                'OCBA_CODE' => $ocba->OCBA_CODE,
                'OCBA_NAME' => $ocba->OCBA_NAME,
                'val' => (int)$count->val,
            ));   
        }        

        $ranking = (object) $ranking;
        usort($ranking, array('bgModelbg', 'cmp'));    
        
        $db->setQuery("SELECT MAX(USCE_UPDATE) AS last_update FROM usce JOIN ($ocba_sql) AS ocba ON USCE_OCBA_CODE = OCBA_CODE WHERE USCE_DATE >= '$filter[from]' AND USCE_DATE <= '$filter[to]'");
        $last_update = strtotime($db->loadObject()->last_update);

        return array(
            'data' => $ranking,
            'last_update' => $last_update == 0 ? "----年--月--日" : date('Y年m月d日',$last_update),
        );
    }
    
    /**
     * Rank club
     */
    public function rank_club($filter) {
        $data = array();
        $last_update = 0;
        
        //get the DB object
        $db = $this->_db;
        
        //get list of OCBA 
        $where_ocba = "";
        $where_ocba .= $filter['area'] == 'all' ? "" : "AND PREF_AREA_CODE = $filter[area] ";
        $where_ocba .= $filter['pref'] == 'all' ? "" : "AND PREF_CODE = $filter[pref] ";
        
        $ocba_sql = "SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE
                        FROM ocba
                        LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                        LEFT JOIN pref  ON TOWN_PREF_CODE = PREF_CODE 
                    WHERE OCBA_FLAG = 1 $where_ocba AND LEFT(OCBA_CODE,1) = '1' ";
        $db->setQuery($ocba_sql);
        $ocbas = $db->loadObjectList();           
         
        $ranking = array();
        foreach ($ocbas as $ocba) {
            $sql = "SELECT SUM(USCL_VAL) AS val ".
                "FROM uscl ".
                "JOIN pacm ON USCL_PACM_CODE = PACM_CODE AND PACM_NUMF = 1 ".
                "WHERE USCL_OCBA_CODE = '$ocba->OCBA_CODE' AND USCL_DATE >= '$filter[from]' AND USCL_DATE <= '$filter[to]' ".
                "GROUP BY USCL_OCBA_CODE ORDER BY val DESC " ;
            $db->setQuery($sql);
            $count = $db->loadObject();   
            array_push($ranking, (object)array(
                'OCBA_CODE' => $ocba->OCBA_CODE,
                'OCBA_NAME' => $ocba->OCBA_NAME,
                'val' => (int)$count->val,
            ));   
        }        

        $ranking = (object) $ranking;
        usort($ranking, array('bgModelbg', 'cmp')); 
        
        $db->setQuery("SELECT MAX(USCL_UPDATE) AS last_update FROM uscl JOIN ($ocba_sql) AS ocba ON USCL_OCBA_CODE = OCBA_CODE WHERE USCL_DATE >= '$filter[from]' AND USCL_DATE <= '$filter[to]'");
        $last_update = strtotime($db->loadObject()->last_update);
        //echo "SELECT MAX(USCL_UPDATE) AS last_update FROM uscl JOIN ($ocba_sql) AS ocba ON USCL_OCBA_CODE = OCBA_CODE WHERE USCL_DATE >= '$filter[from]' AND USCL_DATE <= '$filter[to]'";

        return array(
            'data' => $ranking,
            'last_update' => $last_update == 0 ? "----年--月--日" : date('Y年m月d日',$last_update),
        );
        
        
        
    }
    
    /**
      * get ocba by code
      */     
    public function getOcba($code){
        $this->_db->setQuery("SELECT * FROM ocba WHERE OCBA_CODE = '$code'");
        return $this->_db->loadObject();
    }
    
    public function getUserOcba($user_id){
        $this->_db->setQuery("SELECT USEM_OCBA_CODE FROM kc2vr_users WHERE id = $user_id");
        return $this->_db->loadObject()->USEM_OCBA_CODE;
    }

    public function cmp($a, $b) {
        if($a->val == $b->val){ return 0 ; }
        return ($a->val > $b->val) ? -1 : 1;
    }
    
    
    /**
     * Statistic for top-page
     */
     public function statisticTop($facility){
        $db = $this->_db;
        $last_update = "";
        $data = array();
        $head = array();
        
        $where_facility = "";
        switch($facility){               
            case 'all_gym':
                $where_facility .= " AND USFM_KNAME = '体育館'";
                break;
                
            case 'gym1':
                $where_facility .= " AND USFM_NAME = '第一体育館'";
                break;
                
            case 'gym2':
                $where_facility .= " AND USFM_NAME = '第二体育館'";
                break;
                
            case 'pool':
                $where_facility .= " AND USFM_KNAME = 'プール'";
                break;
                
            case 'dock':
                $where_facility .= " AND USFM_KNAME = '艇庫'";
                break;
            case 'all':
            default:
                break;
        }
        $facility = "(SELECT * FROM usfm WHERE 1=1 $where_facility)";
        $db->setQuery($facility);
        $facility_list = $db->loadObjectList();
        
        //push header
        array_push($head, '年');
        for($i = 1; $i <= 12; $i++){
            $month = $i + 3;
            if($month > 12) {
                $month -= 12;
            }
            array_push($head, $month . '月');
        }
        array_push($data, $head);
        
        //calculate data       
        for($year = date('Y'); $year > date('Y') - 3; $year--){
            $tmp = array();
            array_push($tmp, $year . '年');
            for($i = 1; $i <= 12; $i++){
                $month = $i + 3;
                if($month > 12) {
                    $month -= 12;
                }
                
                $time = $year . "/" . str_pad($month,2,'0',STR_PAD_LEFT);
                
                $sql = 
                    "SELECT SUM(USCE_VAL) AS val, MAX(USCE_UPDATE) AS last_update FROM usce " .
                    "JOIN $facility as usfm ON USCE_USFM_CODE = USFM_CODE ". 
                    "JOIN uscm ON USCE_USCM_CODE = USCM_CODE AND USCM_FLAG = 1 AND USCM_NUMF = 1 " .
                    "WHERE " .
                        "USCE_DATE = '$time' ";
                //echo $sql . "<br>";
                $db->setQuery($sql);
                $result = $db->loadObject();
                $val = empty($result->val) ? 0 : $result->val;
                if(!empty($result->last_update) && (strtotime($result->last_update) > $last_update)){
                    $last_update = strtotime($result->last_update);
                }
                
                array_push($tmp, (int)$val);                
            }
            array_push($data, $tmp);
            
        }
        echo json_encode($data);
        die;
        
     }
}
